package com.harboursoftware.xstorage.db.sql;

import java.sql.Connection;
import java.sql.SQLException;

import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.harboursoftware.xstorage.XStorageException;
import com.harboursoftware.xstorage.db.UserDao;
import com.harboursoftware.xstorage.model.XUser;
import com.harboursoftware.xstorage.util.UUIDGenerator;

/**
 * <p>
 * This class is thread-safe.
 * </p>
 * 
 * @author Simon Leung
 * @since 0.1
 */
public class UserSqlDao implements UserDao {
    private final static Logger log = LoggerFactory.getLogger(UserSqlDao.class);
    
    private RowProcessor userRowProcessor = new UserRowProcessor();
    private ResultSetHandler<XUser> userBeanHandler = new BeanHandler<XUser>(XUser.class, userRowProcessor);
    private DatabaseProvider databaseProvider;
    private QueryRunner sqlRunner = new QueryRunner();
    
    public UserSqlDao(DatabaseProvider databaseProvider) {
        this.databaseProvider = databaseProvider;
    }
    
    @Override
    public XUser findUserById(String id) throws XStorageException {
        String sql = "SELECT * FROM x_user WHERE id=?";
        XUser user = executeAndConvertFirstRowToUser(sql, id);
        log.debug("find user by id[{}],result :[{}]", id, user);
        return user;
    }

    @Override
    public XUser findUserByAccessKeyId(String accessKeyId) throws XStorageException {
        String sql = "SELECT * FROM x_user WHERE access_key_id=?";
        XUser user = executeAndConvertFirstRowToUser(sql, accessKeyId);
        log.debug("find user by accessKeyId[{}],result :[{}]", accessKeyId, user);
        return user;
    }

    @Override
    public XUser findUserByEmail(String email) throws XStorageException {
        String sql = "SELECT * FROM x_user WHERE email=?";
        XUser user = executeAndConvertFirstRowToUser(sql, email);
        log.debug("find user by email[{}],result :[{}]", email, user);
        return user;
    }

    /**
     * execute select query, and converts the first row to XUSer instance.
     * 
     * @param sql
     * @param parameters
     * @return
     * @throws XStorageException
     */
    private XUser executeAndConvertFirstRowToUser(String sql, Object... parameters) throws XStorageException {
        Connection connection = getConnection();
        try {
            XUser user = sqlRunner.query(connection, sql, userBeanHandler, parameters);
            return user;
        } catch (SQLException e) {
            throw new XStorageException("fail to select from database, sql : [" + sql + "]", e);
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }

    @Override
    public void saveUser(XUser user) throws XStorageException {
        Connection connection = getConnection();
        String sql =
            "INSERT INTO x_user (id, access_key_id, secret_access_key, email, display_name) VALUES (?,?,?,?,?)";
        String id = UUIDGenerator.generateUUID();
        Object[] parameters =
            new Object[] { id, user.getAccessKeyId(), user.getSecretAccessKey(), user.getEmail(), user.getDisplayName() };
        try {
            sqlRunner.update(connection, sql, parameters);
            log.debug("save user [{}]", user);
        } catch (SQLException e) {
            throw new XStorageException("fail to save user [" + user + "]", e);
        } finally {
            DbUtils.closeQuietly(connection);
        }
    }
    
    private Connection getConnection() throws XStorageException {
        try {
            return databaseProvider.getConnection();
        } catch (SQLException e) {
            throw new XStorageException("fail to get database connection", e);
        }
    }
}
